package com.lewin;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
/**
 * This class used : open/close Connect to DB and create Query
 * @author kobe
 */
public class ConnectDB {
 
Connection connect = null;
 Statement stmt = null;
 ResultSet rs = null;
 
/**
 * Check Driver connect to MySQL
 */
 protected void hasDriver() throws Exception{
 try {
 Class.forName("com.mysql.jdbc.Driver");
 } catch (ClassNotFoundException ex) {
 throw new Exception ("Invalid Driver!!Please check this drver....");
 }
 }
 
/**
 * Function used to get the connection to the Database
 * Step 1 - I check my connection or not!!
 * Step 2 - If not, it will be null and initialization.
 * Step 3 - Then it return
 * @return Connection
 */
 protected Connection openConnect() throws Exception{
 if(connect == null){
 hasDriver();
 String url="jdbc:mysql://127.0.0.1:3306/quanlycuahangbandothethao";
 // url_sql = "....";
 try {
 this.connect = DriverManager.getConnection(url,"root","061816806");
 } catch (SQLException e) {
 throw new Exception(e.getMessage() + "Connect failed to database .... ");
 }
 }
 return connect;
 }
 /**
 * Make a Statement to execute the SQL statement
 * @return Statement
 */
 protected Statement getStatement() throws SQLException, Exception{
 if(stmt == null){
 stmt = openConnect().createStatement();
 }
 return stmt;
 }
 /**
 * Used to execute the Select statement
 * @param strSQL Query VD: Select * from Employee
 * @return ResultSet
 */
 public ArrayList<SanPham> getAllProducts() throws Exception{
 ArrayList<SanPham> lst = new ArrayList<SanPham>();
 String strSQL = "select * from mathang";
 try {
 rs = getStatement().executeQuery(strSQL);
 while(rs.next()){
 String MaMH = rs.getString("MaMH");
 String TenMH = rs.getString("TenMH");
 String LoaiMH = rs.getString("LoaiMH");
 double DonGia = Double.parseDouble(rs.getString("DonGia"));
 String Hinh = rs.getString("Hinh");
 SanPham sp = new SanPham(MaMH, TenMH, LoaiMH, DonGia,Hinh);
 lst.add(sp);
 }
 } catch (Exception e) {
 throw new Exception(e.getMessage() +" Error at : " + strSQL);
 }
 closeConnet();
 return lst;
 }
 
 public boolean insertNew(SanPham sp) throws Exception{
 String sql = "insert into mathang values(?,?,?,?,?)";
 PreparedStatement pst = openConnect().prepareStatement(sql);
 pst.setString(1, sp.getMaMH());
 pst.setString(2, sp.getTenMH());
 pst.setString(3, sp.getLoaiMH());
 pst.setDouble(4, sp.getDonGia());
 pst.setString(5,sp.getHinh());
 
 return pst.executeUpdate() > 0;
 
 }
 
 public boolean deleteProduct(String masp) throws Exception{
 String sql = "delete from mathang where MaMH=?";
 PreparedStatement pst = openConnect().prepareStatement(sql);
 pst.setString(1, masp);
 return pst.executeUpdate() > 0;
 }
 
 /**
 * Used to execute the Insert, Update, Delete statement
 * @param strSQL Query VD: Insert into TableName values ('??','??')
 * @return The number of lines affected by the command
 */
 public int executeUpdate(String strSQL) throws Exception{
 int result = 0;
 try {
 result = getStatement().executeUpdate(strSQL);
 } catch (Exception ex) {
 throw new Exception(ex.getMessage() + " Error at: " + strSQL);
 }finally{
 this.closeConnet();
 }
 return result;
 }
 
 public SanPham getProductByID(String masp)throws Exception{
 String sql = "select * from mathang where MaMH=?";
 PreparedStatement pst = openConnect().prepareStatement(sql);
 pst.setString(1, masp);
 ResultSet rs = pst.executeQuery();
 SanPham sp = null;
 if(rs.next()){
	 String MaMH = rs.getString("MaMH");
	 String TenMH = rs.getString("TenMH");
	 String LoaiMH = rs.getString("LoaiMH");
	 double DonGia = Double.parseDouble(rs.getString("DonGia"));
	 String Hinh = rs.getString("Hinh");
 sp = new SanPham(MaMH, TenMH, LoaiMH, DonGia,Hinh);
 }
 return sp;
 }
 
 public boolean UpdateProduct(String masp,SanPham newsp) throws Exception{
 String sql = "update mathang set TenMH=?, LoaiMH=?, DonGia=?,Hinh=? where MaMH=?";
 PreparedStatement pst = openConnect().prepareStatement(sql);
 pst.setString(1, newsp.getTenMH());
 pst.setString(2, newsp.getLoaiMH());
 pst.setDouble(3, newsp.getDonGia());
 pst.setString(4, newsp.getHinh());
 pst.setString(5, newsp.getMaMH());
 return pst.executeUpdate()>0;
 }
 /**
 * A method to close the connection.
 * @throws SQLException
 */
 public void closeConnet() throws SQLException{
 if(rs != null && !rs.isClosed())
 rs.close();
 if(stmt != null)
 stmt.close();
 if(connect != null)
 connect.close();
 }
 
 public static void main(String[] args) throws Exception {

 
 }
 
}
